using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;
using PCSComUtils.PCSExc;

namespace PCSComProduction.WorkOrder.DS
{
	/// <summary>
	/// Summary description for PRO_RequiredMaterialMasterDS.
	/// </summary>
	public class PRO_RequiredMaterialMasterDS 
	{
		private const string THIS = "PCSComProduction.WorkOrder.DS.PRO_RequiredMaterialMasterDS";
		public PRO_RequiredMaterialMasterDS()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		public Object GetObjectVO(int pintID)
		{
			throw new NotImplementedException();
		}

		public DataSet List()
		{
			throw new NotImplementedException();
		}

		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql=	"DELETE " + PRO_RequiredMaterialMasterTable.TABLE_NAME 
				+ " WHERE  " + PRO_RequiredMaterialMasterTable.TABLE_NAME + "." + PRO_RequiredMaterialMasterTable.REQUIREMATERIALMASTERID_FLD  + "=" + pintID.ToString();
			OleDbConnection oconPCS=null;
			OleDbCommand ocmdPCS =null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
				ocmdPCS = null;

			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLCASCADE_PREVENT_KEYCODE)
					{
						throw new PCSDBException(ErrorCode.CASCADE_DELETE_PREVENT, METHOD_NAME, ex);				
					}
				}
				else
				{
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Update(Object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			PRO_RequiredMaterialMasterVO objObject = (PRO_RequiredMaterialMasterVO) pobjObjecVO;
			

			//prepare value for parameters
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql=	"UPDATE PRO_RequiredMaterialMaster SET "
					+ PRO_RequiredMaterialMasterTable.ISSUEPURPOSEID_FLD + "=   ?" + ","
					+ PRO_RequiredMaterialMasterTable.LASTUPDATE_FLD + "=   ?" + ","
					+ PRO_RequiredMaterialMasterTable.PRODUCTIONLINEID_FLD + "=   ?" + ","
					+ PRO_RequiredMaterialMasterTable.SALEORDERDETAILID_FLD + "=   ?" + ","
					+ PRO_RequiredMaterialMasterTable.TRANSDATE_FLD + "=   ?" + ","
					+ PRO_RequiredMaterialMasterTable.TRANSNO_FLD + "=   ?" + ","
					+ PRO_RequiredMaterialMasterTable.USERAPPROVAL_FLD + "=  ?" 
					+" WHERE " + PRO_RequiredMaterialMasterTable.REQUIREMATERIALMASTERID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.ISSUEPURPOSEID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.ISSUEPURPOSEID_FLD].Value = objObject.IssuePurposeID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.LASTUPDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.LASTUPDATE_FLD].Value = objObject.LastUpdate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.PRODUCTIONLINEID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.PRODUCTIONLINEID_FLD].Value = objObject.ProductionLineID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.SALEORDERDETAILID_FLD].Value = objObject.SaleOrderDetailID;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.TRANSDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.TRANSDATE_FLD].Value = objObject.TransDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.TRANSNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.TRANSNO_FLD].Value = objObject.TransNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.USERAPPROVAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.USERAPPROVAL_FLD].Value = objObject.UserApproval;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.REQUIREMATERIALMASTERID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.REQUIREMATERIALMASTERID_FLD].Value = objObject.RequiredMaterialMasterID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();	
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
					else
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
				else
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public void Add(Object pobjObjectVO)
		{
			throw new NotImplementedException();
		}

		public void UpdateDataSet(DataSet pData)
		{
			throw new NotImplementedException();
		}

		public int AddAndReturnID(object objectMaster)
		{
			const string METHOD_NAME = THIS + ".Add()";
			 
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS =null;
			try
			{
				PRO_RequiredMaterialMasterVO objObject = (PRO_RequiredMaterialMasterVO) objectMaster;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);
				
				strSql=	"INSERT INTO PRO_RequiredMaterialMaster("
					+ PRO_RequiredMaterialMasterTable.PRODUCTIONLINEID_FLD + ","
					+ PRO_RequiredMaterialMasterTable.ISSUEPURPOSEID_FLD + ","
					+ PRO_RequiredMaterialMasterTable.SALEORDERDETAILID_FLD + ","
					+ PRO_RequiredMaterialMasterTable.TRANSNO_FLD + ","
					+ PRO_RequiredMaterialMasterTable.USERNAME_FLD + ","
					+ PRO_RequiredMaterialMasterTable.USERAPPROVAL_FLD + ","
					+ PRO_RequiredMaterialMasterTable.LASTUPDATE_FLD + ","
					+ PRO_RequiredMaterialMasterTable.TRANSDATE_FLD + ")"
					+ " VALUES(?,?,?,?,?,?,?,?) "
					+ "SELECT @@IDENTITY";

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.PRODUCTIONLINEID_FLD, OleDbType.Integer));
				if (objObject.ProductionLineID != 0)
				{
					ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.PRODUCTIONLINEID_FLD].Value = objObject.ProductionLineID;
				}
				else
					ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.PRODUCTIONLINEID_FLD].Value = DBNull.Value;
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.ISSUEPURPOSEID_FLD, OleDbType.Integer));
				if (objObject.IssuePurposeID != 0)
				{
					ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.ISSUEPURPOSEID_FLD].Value = objObject.IssuePurposeID;
				}
				else
					ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.ISSUEPURPOSEID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.SALEORDERDETAILID_FLD, OleDbType.Integer));
				if (objObject.SaleOrderDetailID != 0)
				{
					ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.SALEORDERDETAILID_FLD].Value = objObject.SaleOrderDetailID;
				}
				else
					ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.SALEORDERDETAILID_FLD].Value = DBNull.Value;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.TRANSNO_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.TRANSNO_FLD].Value = objObject.TransNo;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.USERNAME_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.USERNAME_FLD].Value = objObject.Username;
				
				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.USERAPPROVAL_FLD, OleDbType.VarWChar));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.USERAPPROVAL_FLD].Value = objObject.UserApproval;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_RequiredMaterialMasterTable.LASTUPDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.LASTUPDATE_FLD].Value = objObject.LastUpdate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PRO_WorkOrderMasterTable.TRANSDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PRO_RequiredMaterialMasterTable.TRANSDATE_FLD].Value = objObject.TransDate;
				
				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				object objReturn = ocmdPCS.ExecuteScalar();	
				if (objReturn != null)
				{
					return int.Parse(objReturn.ToString());
				}
				else
				{
					return 0;
				}
			}
			catch(OleDbException ex)
			{
				if (ex.Errors.Count > 1)
				{
					if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE)
						throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);	
					else
						throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
				}
				else
					throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			

			catch(InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public DataRow GetProductInfo(int pintSaleOrderDetailId)
		{
			const string METHOD_NAME = THIS + ".GetWODetailByMaster()";
			DataSet dstDetail = new DataSet();
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = String.Empty;
				strSql= "Select S.SaleOrderDetailID, P.ProductID, P.Code, P.Description" 
					+ " FROM "+ SO_SaleOrderDetailTable.TABLE_NAME +" S "
					+ " Inner join " + ITM_ProductTable.TABLE_NAME + " P ON S.ProductID = P.ProductID"
					+ " WHERE " + SO_SaleOrderDetailTable.SALEORDERDETAILID_FLD + " = " + pintSaleOrderDetailId;
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstDetail,PRO_WorkOrderDetailTable.TABLE_NAME);

				return dstDetail.Tables[0].Rows[0];
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}

		public bool IsReceived(int pintRequiredMasterID)
		{
			const string METHOD_NAME = THIS + ".IsReceived()";
			OleDbConnection oconPCS =null;
			OleDbCommand ocmdPCS = null;
			try 
			{
				string strSql = "SELECT ActualReceived FROM PRO_RequiredMaterialDetail"
					+ " WHERE ISNULL(ActualReceived,0) = 0"
					+ " AND " + PRO_RequiredMaterialDetailTable.REQUIREDMATERIALMASTERID_FLD + "=" + pintRequiredMasterID;
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();
				DataTable dtbData = new DataTable();
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dtbData);
				if (dtbData.Rows.Count > 0)
					return false;
				else
					return true;
			}
			catch(OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME,ex);
			}			
			catch (Exception ex) 
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
		}
		
	}
}
